# coding=utf-8
import pymssql
from sqlalchemy import create_engine, text, select, MetaData, Table, Column
from src.util.configutil import get_db_config
import pandas as pd
# import pymssql
import pymysql

db_config = get_db_config()


def get_engine():
    username = db_config.get("username")
    password = db_config.get("password")
    host = db_config.get("host")
    port = db_config.get("port")
    dbname = db_config.get("dbname")
    engine = create_engine(f"mysql+pymysql://{username}:{password}@{host}:{port}/{dbname}?charset=utf8")
    return engine


def query_sql(sql, sql_param):
    """查询 select"""
    engine = get_engine()
    query_all = None
    with engine.connect() as conn:
        print(sql)
        print(sql_param)
        result = conn.execute(text(sql), sql_param)
        query_all = result.fetchall()
    return query_all


def execute_sql(sql, sql_param=None):
    """执行SQL delete update insert"""
    engine = get_engine()
    result = None
    with engine.connect() as conn:
        result = conn.execute(text(sql), sql_param)
        conn.commit()
    return result


def query_sql_by_pandas(sql, sql_param=None):
    engine = get_engine()
    return pd.read_sql_query(sql, engine, params=sql_param)


def df_to_model_list(df):
    total = df.count()[0]
    if total == 0:
        return []
    list_dic = []
    headers = df.columns
    for i in range(total):
        series = df.iloc[i]
        model = {}
        for column in headers:
            model.setdefault(str(column), str(series[column]))
        list_dic.append(model)
    return list_dic


def execute_sql_new(sql, param=()):
    """执行sql语句"""
    # db = cx_Oracle.connect('RPA_USER', 'skyRPA#1375', '172.20.215.209:1521/orcl')
    # db = cx_Oracle.connect(host='172.20.215.209', user='FR_USER', password='FR_USER.1234', database='ORCL')
    # cursor = db.cursor()
    # ('172.20.215.209', 'FR_USER', 'FR_USER.1234', 1521, 'ORCL')
    username = db_config.get("username")
    password = db_config.get("password")
    host = db_config.get("host")
    port = db_config.get("port")
    dbname = db_config.get("dbname")
    # db = pymssql.connect(server=host, user=username, password=password, database=dbname, port=port)  # 建立连接 pymssql
    db = pymysql.connect(host=host, port=port, user=username, password=password, db=dbname)
    cursor = db.cursor()
    try:
        cursor.execute(sql, param)
        # result_1= cursor.fetchall()
        db.commit()
        return True
    except Exception as e:
        # log = Log()
        # log.setLog(str(e), {"func": 'execute_sql_new', "args": str(param), "kwargs": str(param)})
        # write_log(log=log)
        print(f"执行sql报错:{sql}-{param}", e)
        db.rollback()  # 加个try 报错就用rollback回滚
    cursor.close()
    return False


def execute_sql_new_many(sql, param=None):
    """执行sql语句"""
    username = db_config.get("username")
    password = db_config.get("password")
    host = db_config.get("host")
    port = db_config.get("port")
    dbname = db_config.get("dbname")
    # db = pymssql.connect(server=host, user=username, password=password, database=dbname, port=port)  # 建立连接 pymssql
    db = pymysql.connect(host=host, port=port, user=username, password=password, db=dbname)
    cursor = db.cursor()
    try:
        cursor.execute(sql, param)
        # result_1= cursor.fetchall()
        db.commit()
        return True
    except Exception as e:
        print(e)
        db.rollback()  # 加个try 报错就用rollback回滚
    cursor.close()
    return False


def pandas_to_sql(df, table_name):
    """将dataframe导入数据库"""
    if df is None or df.empty:
        return None
    engine = get_engine()
    df.to_sql(table_name, con=engine, index=False, if_exists="append")


def create_page_sql(sql, page, limit):
    """创建分页sql"""
    start = (page - 1) * limit
    end = limit
    page_sql = f"""
        select * from ({sql}) T limit {start} , {end}
    """
    return page_sql


def create_total_sql(sql):
    """获取总数"""
    ct_sql = """select count(*) ct from ({0}) T """.format(sql)
    return ct_sql


def get_first_scaner(df, default=0):
    if df.empty:
        return default
    return df.iloc[0][0]
